package com.sq.partner.manager.dao.impl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;

import org.apache.log4j.Logger;
import org.springframework.stereotype.Repository;

import com.sq.partner.manager.dao.NocarEdrnoPowerDao;
import com.sq.partner.manager.mybatis.model.GatewayUser;
import com.sq.partner.manager.mybatis.model.NocarEdrnoPower;
import com.sq.partner.manager.util.DBUtil;
import com.sq.partner.manager.util.LogUtil;
import com.sq.partner.manager.util.Page;
import com.sq.partner.manager.util.SqlUtil;
@Repository
public class NocarEdrnoPowerDaoImpl extends BaseDaoImpl implements NocarEdrnoPowerDao {
	private static Logger logger = LogUtil.getLog(NocarEdrnoPowerDaoImpl.class);
	@Override
	public Page<Map<String,Object>> queryNocarEdrnoPowers(Page<Map<String,Object>> page)
			throws SQLException {
		Map<String, Object> map = page.getMap();
		long totel =  (Long) this.query("queryEdrnoCountByFilter", map);
		page.setTotal(totel);
		List<Map<String,Object>> queryList = this.queryList("queryNocarEdrnoPowerPage", map);
		page.setData(queryList);
		return page;
	}

	@Override
	public boolean deletNocarEdrnoPower(String id) throws Exception {
			return this.delete("deleteNocarEdrnoById", id);
	}

	@Override
	public boolean addNocarEdrnoPower(NocarEdrnoPower nocarEdrnoPower)
			throws Exception {
		return this.add("insertNocarEdrnoPower", nocarEdrnoPower);
	}

	@Override
	public boolean updateNocarEdrnoPower(NocarEdrnoPower nocarEdrnoPower)
			throws Exception {
		return this.update("updateNocarEdrnoPower", nocarEdrnoPower);
	}

	@Override
	public NocarEdrnoPower queryNocarEdrnoPowerById(String id) throws Exception {
		return (NocarEdrnoPower) this.query("queryNocarEdrnoPowerById", id);
	}

	@Override
	public Page<Map<String, Object>> queryPlanPageByCriteria(Page<Map<String, Object>> page,
			GatewayUser user) throws Exception {
		Connection conn = null;
		//查总条数
		PreparedStatement pstmt = null;
		//查分页
		PreparedStatement pstmt1 = null;
		ResultSet rs = null;
		try{
			conn = this.getConnection();
			StringBuffer sqlbuf=new StringBuffer();
			sqlbuf.append("  select plan.plan_name, plan.plan_code from t_nocar_plan plan ");
			sqlbuf.append(" inner join t_gateway_channel_info info on plan.partner_code = info.channel_code ");
			sqlbuf.append(" where info.trans_type = '2' and plan.status='1'  and info.dept_code in ( select t.dept_code  from t_core_dept t  connect by prior dept_code = snr_dept  start with dept_code in ( "+user.getDepartmentStr()+" ))");
			Map<String, Object> map=page.getMap();
			
			List<String> list=new ArrayList<String>();
			List<Boolean> islike=new ArrayList<Boolean>();
			if(map.get("partnerNo")!=null){
				 	sqlbuf.append(" and plan.partner_code= ?");
				    list.add((String) map.get("partnerNo"));
				    islike.add(false);
			}
			if(map.get("planCode")!=null){
				sqlbuf.append(" and plan.plan_code like ?");
				String planCode=(String) map.get("planCode");
				list.add(planCode);
				islike.add(true);
			}
			
			if(map.get("planName")!=null){
				sqlbuf.append(" and plan.plan_Name like ?");
				String planName=(String) map.get("planName");
				list.add(planName);
				islike.add(true);
			}
			String sql;
			sqlbuf.append(" order by plan.plan_code desc");
			sql=sqlbuf.toString();
			logger.debug("执行SQL：" + sql);

			pstmt = conn.prepareStatement("select count(*) from ("+sql+")");
			for (int i = 0; i < list.size(); i++) {
				if(islike.get(i)){
					pstmt.setObject(i+1, "%"+list.get(i)+"%");
				}else{
					pstmt.setObject(i+1, list.get(i));
				}
				
			}
			rs = pstmt.executeQuery();
			rs.next();
			Long total = rs.getLong(1);
			
			//查出分页
			page.setTotal(total);
			SqlUtil sqlUtil=new	SqlUtil();
			String pagesql=sqlUtil.buildPageSqlForOracle(sql,page).toString();
			logger.debug("执行SQL："+pagesql);
			pstmt1 = conn.prepareStatement(pagesql);
			for (int i = 0; i < list.size(); i++) {
				if(islike.get(i)){
					pstmt1.setObject(i+1, "%"+list.get(i)+"%");
				}else{
					pstmt1.setObject(i+1, list.get(i));
				}
			}
			rs = pstmt1.executeQuery();
			List lists = DBUtil.transRs2List(rs);
			page.setData(lists);
		}finally{
			close(rs);
			close(pstmt);
			close(pstmt1);
			close(conn);
		}
		return page;
	}
}
